package com.ygq.exportdbstructure.service.impl;

import com.ygq.exportdbstructure.model.DataSource;
import com.ygq.exportdbstructure.service.ExportDBService;
import com.ygq.exportdbstructure.utils.ExportDBExcelUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author emc
 * @version 1.0
 * createTime 2025-10-29
 * description 导出数据库接口
 */
@Slf4j
@Service
public class ExportDBServiceImpl implements ExportDBService {


    /**
     * 获取数据库连接
     * @param dataSource 数据库连接信息
     * @return 数据库连接
     */
    @Override
    public Connection getDBConnection(DataSource dataSource) {
        Connection connection;
        try {
            // 根据数据库类型构建对应的JDBC URL
            String url = buildJdbcUrl(dataSource);

            // 加载数据库驱动类
            Class.forName(dataSource.getDriverClassName());
            log.info("数据库驱动加载成功: " + dataSource.getDriverClassName());

            // 建立数据库连接
            connection = DriverManager.getConnection(url, dataSource.getUsername(), dataSource.getPassword());
            log.info("数据库连接成功: " + url);

        } catch (ClassNotFoundException e) {
            log.error("数据库驱动加载失败: " + e.getMessage());
            e.printStackTrace();
            throw new RuntimeException("数据库驱动加载失败", e);
        } catch (SQLException e) {
            log.error("数据库连接失败: " + e.getMessage());
            e.printStackTrace();
            throw new RuntimeException("数据库连接失败", e);
        }
        return connection;
    }

    /**
     * 根据数据库类型构建JDBC URL
     */
    private String buildJdbcUrl(DataSource dataSource) {
        String dbType = dataSource.getDb_type().toLowerCase();

        switch (dbType) {
            case "mysql":
                return String.format("jdbc:mysql://%s:%s/%s",
                        dataSource.getDb_ip(), dataSource.getDb_port(), dataSource.getDb_name());

            case "oracle":
                return String.format("jdbc:oracle:thin:@%s:%s:%s",
                        dataSource.getDb_ip(), dataSource.getDb_port(), dataSource.getDb_name());

            case "dm": // 达梦数据库
                return String.format("jdbc:dm://%s:%s/%s",
                        dataSource.getDb_ip(), dataSource.getDb_port(), dataSource.getDb_name());

            case "sqlserver":
                return String.format("jdbc:sqlserver://%s:%s;databaseName=%s",
                        dataSource.getDb_ip(), dataSource.getDb_port(), dataSource.getDb_name());

            case "doris":
                return String.format("jdbc:mysql://%s:%s/%s",
                        dataSource.getDb_ip(), dataSource.getDb_port(), dataSource.getDb_name());

            case "postgresql":
                return String.format("jdbc:postgresql://%s:%s/%s",
                        dataSource.getDb_ip(), dataSource.getDb_port(), dataSource.getDb_name());

            default:
                throw new IllegalArgumentException("不支持的数据库类型: " + dbType);
        }
    }


    /**
     * 获取表结构信息
     * @param connection 数据库连接
     * @param schema 数据库模式
     * @param tableName 表名
     * @return 表结构信息
     */
    public List<Map<String, Object>> getTableStructure(Connection connection, String schema, String tableName) throws SQLException {
        List<Map<String, Object>> result = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();

        // 获取主键信息
        List<String> pkColumns = new ArrayList<>();
        try (ResultSet primaryKeys = metaData.getPrimaryKeys(null, schema, tableName)) {
            while (primaryKeys.next()) {
                pkColumns.add(primaryKeys.getString("COLUMN_NAME"));
            }
        }

        // 获取列信息
        try (ResultSet columns = metaData.getColumns(null, schema, tableName, null)) {
            while (columns.next()) {
                Map<String, Object> columnInfo = new HashMap<>();

                columnInfo.put("COLUMN_NAME", columns.getString("COLUMN_NAME"));
                String typeName = columns.getString("TYPE_NAME");
                columnInfo.put("TYPE_NAME", typeName);
                columnInfo.put("COLUMN_SIZE", columns.getInt("COLUMN_SIZE"));
                columnInfo.put("IS_NULLABLE", columns.getString("IS_NULLABLE"));
                columnInfo.put("COLUMN_DEF", columns.getString("COLUMN_DEF"));

                // 获取小数精度信息
                int decimalDigits = columns.getInt("DECIMAL_DIGITS");
                columnInfo.put("DECIMAL_DIGITS", decimalDigits);

                // 计算并添加精度信息
                if (decimalDigits > 0) {
                    int precision = columns.getInt("COLUMN_SIZE");
                    columnInfo.put("PRECISION", precision);
                    columnInfo.put("SCALE", decimalDigits);
                    columnInfo.put("PRECISION_INFO", precision + "," + decimalDigits);
                } else {
                    columnInfo.put("PRECISION", columns.getInt("COLUMN_SIZE"));
                    columnInfo.put("SCALE", 0);
                    columnInfo.put("PRECISION_INFO", columns.getInt("COLUMN_SIZE") + ",0");
                }

                columnInfo.put("NUM_PREC_RADIX", columns.getInt("NUM_PREC_RADIX"));
                columnInfo.put("REMARKS", columns.getString("REMARKS"));
                columnInfo.put("CHAR_OCTET_LENGTH", columns.getInt("CHAR_OCTET_LENGTH"));
                columnInfo.put("ORDINAL_POSITION", columns.getInt("ORDINAL_POSITION"));
                columnInfo.put("IS_AUTOINCREMENT", columns.getString("IS_AUTOINCREMENT"));
                columnInfo.put("IS_PRIMARY_KEY", pkColumns.contains(columns.getString("COLUMN_NAME")));

                result.add(columnInfo);
            }
        }

        return result;
    }

    /**
     * 获取表结构信息并生成Excel文件
     * @param dataSource 数据源信息
     * @return 生成的Excel文件
     */
    @Override
    public byte[] getTableStructureExcel(DataSource dataSource) {
        // 获取数据库连接
        Connection connection = getDBConnection(dataSource);
        try {
            List<Map<String, Object>> tableStructure = getTableStructure(connection, dataSource.getDb_name(), dataSource.getDb_table_name());
            return ExportDBExcelUtils.createExcelFile(tableStructure, dataSource.getDb_table_name());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
